/**
 * Filename: DBManager.java
 *
 * @author Firefly
 * @contact postfirefly@163.com
 * @time 2016/3/17 16:08
 */
package com.github.postsanf.yinian.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.github.postsanf.yinian.utils.StringUtils;
import com.github.postsanf.yinian.utils.YNDoSecretUtils;

import java.util.ArrayList;
import java.util.List;

public class DBManager {
    private DBHelper helper;
    private SQLiteDatabase db;

    public DBManager(Context context) {
        helper = new DBHelper(context);
        //因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
        //所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = helper.getWritableDatabase();
    }

    // 添加上传进度信息到进度信息数据库
    public int addUpProgress(YNUpProgress upProgress) {
        int strid = 0;
        db.beginTransaction();  //开始事务
        try {
            db.execSQL("INSERT INTO upprogress VALUES(null,? ,?,?,? ,? ,? ,? ,? ,?,? ,? ,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new Object[]{upProgress.upuid, upProgress.upgid, upProgress.upgimid, upProgress.upisofficial, upProgress.upgname, upProgress.uptype, upProgress.uppush, upProgress.upcontent, upProgress.upeplace, upProgress.uptime, upProgress.upsize, upProgress.upstatus, upProgress.upfirstimg, upProgress.upismemorycard, upProgress.uptags, upProgress.upaudio, upProgress.upstorage, upProgress.upmemorytime, upProgress.upplace, upProgress.upcardstyle, upProgress.upaudiotime, upProgress.upcoverurl,
                    upProgress.upplacepic, upProgress.upplacelongitude, upProgress.upplacelatitude, upProgress.uppeoplename, upProgress.upmain, upProgress.upsource, upProgress.upispush, upProgress.verifypicaddress, upProgress.totalnum, upProgress.totalmoney});
            Cursor cursor = db.rawQuery("select last_insert_rowid() from upprogress", null);
            if (cursor.moveToFirst()) {
                strid = cursor.getInt(0);
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } finally {
            db.endTransaction();    //结束事务
        }
        return strid;
    }

    // 添加上传图片到图片上传数据数据库
    public void addUpImgKey(List<YNUpImgKey> upImgKeys) {
        db.beginTransaction();  //开始事务
        try {
            for (YNUpImgKey upImgKey : upImgKeys) {
                db.execSQL("INSERT INTO upimgkey VALUES(null,? ,? ,? ,?)", new Object[]{upImgKey.upid, upImgKey.upaddress, upImgKey.upimgsize, upImgKey.upkey});
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    // 通过UpID 获取上传失败的图片列表
    public ArrayList<YNUpImgKey> queryNullKeyById(int upId) {
        ArrayList<YNUpImgKey> upImgKeys = new ArrayList<YNUpImgKey>();
        // 查询指定UpId且key值为空图片列表
        String sql = "SELECT * FROM upimgkey WHERE upid = ? AND upkey = ? ";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(upId), ""});
        while (c.moveToNext()) {
            YNUpImgKey upImgKey = new YNUpImgKey();
            upImgKey.id = c.getInt(c.getColumnIndex("id"));
            upImgKey.upid = c.getInt(c.getColumnIndex("upid"));
            upImgKey.upaddress = c.getString(c.getColumnIndex("upaddress"));
            upImgKey.upimgsize = c.getString(c.getColumnIndex("upimgsize"));
            upImgKey.upkey = c.getString(c.getColumnIndex("upkey"));
            upImgKeys.add(upImgKey);
        }
        c.close();
        return upImgKeys;
    }

    // 通过UpId获取已上传图片的Size
    public long getUpedSizeById(int upid) {
        long res = 0;
        String sql = "SELECT * FROM upimgkey WHERE upid = ?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(upid)});
        while (c.moveToNext()) {
            long size = Long.parseLong(c.getString(c.getColumnIndex("upimgsize")));
            String key = c.getString(c.getColumnIndex("upkey"));
            if (!StringUtils.isEmptyString(key)) {
                res += size;
            }
        }
        c.close();
        return res;
    }

    // 通过imgKey ID 更新上传key值
    public void updateUpKey(int imgId, String upkey) {
        ContentValues cv = new ContentValues();
        cv.put("upkey", upkey);
        db.update("upimgkey", cv, "id = ?", new String[]{String.valueOf(imgId)});
    }

    // 更新上传状态
    public void updateUpStatus(int id, boolean isStopUp) {
        int upstatus = isStopUp ? 1 : 0;
        ContentValues cv = new ContentValues();
        cv.put("upstatus", upstatus);
        db.update("upprogress", cv, "id = ?", new String[]{String.valueOf(id)});
    }

    public void updateUpSizeById(int id, String upsize) {
        ContentValues cv = new ContentValues();
        cv.put("upsize", upsize);
        db.update("upprogress", cv, "id = ?", new String[]{String.valueOf(id)});
    }

    // 根据指定ID删除上传信息
    public void deleteUpProgressById(int upProgressId) {
        db.delete("upprogress", "id = ?", new String[]{String.valueOf(upProgressId)});
        db.delete("upimgkey", "upid = ?", new String[]{String.valueOf(upProgressId)});
    }

    // 查询所有待上传进度信息
    public List<YNUpProgress> queryUpProgressAll() {
        ArrayList<YNUpProgress> upProgresses = new ArrayList<YNUpProgress>();
        Cursor c = queryTheCursorUpProgress();
        while (c.moveToNext()) {
            YNUpProgress upProgress = new YNUpProgress();
            upProgress.id = c.getInt(c.getColumnIndex("id"));
            upProgress.upuid = YNDoSecretUtils.getSecretStr(c.getString(c.getColumnIndex("upuid")));
            upProgress.upgid = YNDoSecretUtils.getSecretStr(c.getString(c.getColumnIndex("upgid")));
            upProgress.upgimid = c.getString(c.getColumnIndex("upgimid"));
            upProgress.upisofficial = c.getInt(c.getColumnIndex("upisofficial"));
            upProgress.upgname = c.getString(c.getColumnIndex("upgname"));
            upProgress.uptype = c.getInt(c.getColumnIndex("uptype"));
            upProgress.uppush = c.getInt(c.getColumnIndex("uppush"));
            upProgress.upcontent = c.getString(c.getColumnIndex("upcontent"));
            upProgress.upeplace = c.getString(c.getColumnIndex("upeplace"));
            upProgress.uptime = c.getString(c.getColumnIndex("uptime"));
            upProgress.upsize = c.getString(c.getColumnIndex("upsize"));
            upProgress.upstatus = c.getInt(c.getColumnIndex("upstatus"));
            upProgress.upfirstimg = c.getString(c.getColumnIndex("upfirstimg"));
            upProgress.upismemorycard = c.getInt(c.getColumnIndex("upismemorycard"));
            upProgress.uptags = c.getString(c.getColumnIndex("uptags"));
            upProgress.upaudio = c.getString(c.getColumnIndex("upaudio"));
            upProgress.upstorage = c.getString(c.getColumnIndex("upstorage"));
            upProgress.upmemorytime = c.getString(c.getColumnIndex("upmemorytime"));
            upProgress.upplace = c.getString(c.getColumnIndex("upplace"));
            upProgress.upcardstyle = c.getString(c.getColumnIndex("upcardstyle"));
            upProgress.upaudiotime = c.getString(c.getColumnIndex("upaudiotime"));
            upProgress.upcoverurl = c.getString(c.getColumnIndex("upcoverurl"));
            upProgress.upplacepic = c.getString(c.getColumnIndex("upplacepic"));
            upProgress.upplacelongitude = c.getString(c.getColumnIndex("upplacelongitude"));
            upProgress.upplacelatitude = c.getString(c.getColumnIndex("upplacelatitude"));
            upProgress.uppeoplename = c.getString(c.getColumnIndex("uppeoplename"));
            upProgress.upmain = c.getString(c.getColumnIndex("upmain"));
            upProgress.upsource = c.getString(c.getColumnIndex("upsource"));
            upProgress.upispush = c.getString(c.getColumnIndex("upispush"));
            upProgress.verifypicaddress = c.getString(c.getColumnIndex("verifypicaddress"));
            upProgress.totalnum = c.getString(c.getColumnIndex("totalnum"));
            upProgress.totalmoney = c.getString(c.getColumnIndex("totalmoney"));
            upProgresses.add(upProgress);
        }
        c.close();
        return upProgresses;
    }

    // 根据ID查询待上传进度信息
    public YNUpProgress queryUpProgressById(int id) {
        YNUpProgress upProgress = new YNUpProgress();
        String sql = "SELECT * FROM upprogress WHERE id = ?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(id)});
        while (c.moveToNext()) {
            upProgress.id = c.getInt(c.getColumnIndex("id"));
            upProgress.upuid = YNDoSecretUtils.getSecretStr(c.getString(c.getColumnIndex("upuid")));
            upProgress.upgid = YNDoSecretUtils.getSecretStr(c.getString(c.getColumnIndex("upgid")));
            upProgress.upgimid = c.getString(c.getColumnIndex("upgimid"));
            upProgress.upisofficial = c.getInt(c.getColumnIndex("upisofficial"));
            upProgress.upgname = c.getString(c.getColumnIndex("upgname"));
            upProgress.uptype = c.getInt(c.getColumnIndex("uptype"));
            upProgress.uppush = c.getInt(c.getColumnIndex("uppush"));
            upProgress.upcontent = c.getString(c.getColumnIndex("upcontent"));
            upProgress.upeplace = c.getString(c.getColumnIndex("upeplace"));
            upProgress.uptime = c.getString(c.getColumnIndex("uptime"));
            upProgress.upsize = c.getString(c.getColumnIndex("upsize"));
            upProgress.upstatus = c.getInt(c.getColumnIndex("upstatus"));
            upProgress.upfirstimg = c.getString(c.getColumnIndex("upfirstimg"));
            upProgress.upismemorycard = c.getInt(c.getColumnIndex("upismemorycard"));
            upProgress.uptags = c.getString(c.getColumnIndex("uptags"));
            upProgress.upaudio = c.getString(c.getColumnIndex("upaudio"));
            upProgress.upstorage = c.getString(c.getColumnIndex("upstorage"));
            upProgress.upmemorytime = c.getString(c.getColumnIndex("upmemorytime"));
            upProgress.upplace = c.getString(c.getColumnIndex("upplace"));
            upProgress.upcardstyle = c.getString(c.getColumnIndex("upcardstyle"));
            upProgress.upaudiotime = c.getString(c.getColumnIndex("upaudiotime"));
            upProgress.upcoverurl = c.getString(c.getColumnIndex("upcoverurl"));
            upProgress.upplacepic = c.getString(c.getColumnIndex("upplacepic"));
            upProgress.upplacelongitude = c.getString(c.getColumnIndex("upplacelongitude"));
            upProgress.upplacelatitude = c.getString(c.getColumnIndex("upplacelatitude"));
            upProgress.uppeoplename = c.getString(c.getColumnIndex("uppeoplename"));
            upProgress.upmain = c.getString(c.getColumnIndex("upmain"));
            upProgress.upsource = c.getString(c.getColumnIndex("upsource"));
            upProgress.upispush = c.getString(c.getColumnIndex("upispush"));
            upProgress.verifypicaddress = c.getString(c.getColumnIndex("verifypicaddress"));
            upProgress.totalnum = c.getString(c.getColumnIndex("totalnum"));
            upProgress.totalmoney = c.getString(c.getColumnIndex("totalmoney"));
        }
        c.close();
        return upProgress;
    }

    // 根据UpID 获取图片key值列表
    public ArrayList<String> queryUpKeysById(int upid) {
        ArrayList<String> upImgKeys = new ArrayList<String>();
        String sql = "SELECT * FROM upimgkey WHERE upid = ?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(upid)});
        while (c.moveToNext()) {
            upImgKeys.add(c.getString(c.getColumnIndex("upkey")));
        }
        c.close();
        return upImgKeys;
    }

    // 根据上传ID获取上传图片列表信息
    public List<YNUpImgKey> queryUpImgKeyById(int upLoadId) {
        ArrayList<YNUpImgKey> upImgKeys = new ArrayList<YNUpImgKey>();
        String sql = "SELECT * FROM upimgkey WHERE upid = ?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(upLoadId)});
        while (c.moveToNext()) {
            YNUpImgKey upImgKey = new YNUpImgKey();
            upImgKey.id = c.getInt(c.getColumnIndex("id"));
            upImgKey.upid = c.getInt(c.getColumnIndex("upid"));
            upImgKey.upaddress = c.getString(c.getColumnIndex("upaddress"));
            upImgKey.upimgsize = c.getString(c.getColumnIndex("upimgsize"));
            upImgKey.upkey = c.getString(c.getColumnIndex("upkey"));
            upImgKeys.add(upImgKey);
        }
        c.close();
        return upImgKeys;
    }

    // 获取光标位置
    public Cursor queryTheCursorUpProgress() {
        Cursor c = db.rawQuery("SELECT * FROM upprogress", null);
        return c;
    }

    // 删除数据库
    public void closeDB() {
        db.close();
    }
}